﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;

namespace QT.Office
{
    public class Excel
    {
        /// <summary>
        /// 导出ListView内容到xls
        /// </summary>
        /// <param name="listView">ListView</param>
        /// <param name="b">是否导出所有行，为false时仅导出选择的行</param>
        /// <param name="strFilePath">xls文件路径</param>
        /// <returns>bool</returns>
        public static bool ToXLS(ListView listView, bool b, string strFilePath)
        {
            ListView.ListViewItemCollection listViewItems=null;
            if (b) listViewItems = listView.Items;
            else
            {
                foreach (ListViewItem item in listView.SelectedItems) listViewItems.Add(item);
            }
            int rowNum = listViewItems.Count;
            if (rowNum == 0 || string.IsNullOrEmpty(strFilePath))
            {
                return false;
            }

            int columnNum = 0;
            foreach (ColumnHeader col in listView.Columns)
            {
                if (col.Width > 0) columnNum++;
            }

            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");

            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < listView.Columns.Count && listView.Columns[i].Width > 0; i++)
            {
                ICell cell = row.CreateCell(columnNum);
                cell.SetCellValue(listView.Columns[i].Text);
                IFont font = workbook.CreateFont();
                font.Boldweight = (short)FontBoldWeight.BOLD;
                ICellStyle cellstype = workbook.CreateCellStyle();
                cellstype.SetFont(font);
                cellstype.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                cell.CellStyle = cellstype;
                columnNum++;
            }

            for (int i = 0; i < rowNum; i++)
            {
                row = sheet.CreateRow(i + 1);
                columnNum = 0;
                for (int j = 0; j < listView.Columns.Count && listView.Columns[j].Width > 0; j++)
                {
                    row.CreateCell(columnNum).SetCellValue(listViewItems[i].SubItems[j].Text);
                    columnNum++;
                }
            }

            FileStream file = new FileStream(strFilePath, FileMode.Create);
            workbook.Write(file);
            file.Close();
            workbook = null;

            return true;
        }

        /// <summary>
        /// 导出DataGridView内容到xls
        /// </summary>
        /// <param name="dataGridView">DataGridView</param>
        /// <param name="b">是否导出所有行，为false时仅导出选择的行</param>
        /// <param name="strFilePath">xls文件路径</param>
        /// <returns>bool</returns>
        public static bool ToXLS(DataGridView dataGridView, bool b, string strFilePath)
        {
            DataGridViewRowCollection dataGridViewRows = null;
            if (b) dataGridViewRows = dataGridView.Rows;
            else
            {
                foreach (DataGridViewRow Row in dataGridView.SelectedRows) dataGridViewRows.Add(Row);
            }
            int rowNum = dataGridViewRows.Count;
            if (rowNum == 0 || string.IsNullOrEmpty(strFilePath))
            {
                return false;
            }

            int columnNum = 0;

            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");

            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dataGridView.Columns.Count&&dataGridView.Columns[i].Visible; i++)
            {
                ICell cell = row.CreateCell(columnNum);
                cell.SetCellValue(dataGridView.Columns[i].HeaderText);
                IFont font = workbook.CreateFont();
                font.Boldweight = (short)FontBoldWeight.BOLD;
                ICellStyle cellstype = workbook.CreateCellStyle();
                cellstype.SetFont(font);
                cellstype.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                cell.CellStyle = cellstype;
                columnNum++;
            }

            for (int i = 0; i < rowNum; i++)
            {
                row = sheet.CreateRow(i + 1);
                columnNum = 0;
                for (int j = 0; j < columnNum && dataGridView.Columns[i].Visible; j++)
                {
                    row.CreateCell(columnNum).SetCellValue(dataGridViewRows[i].Cells[j].Value.ToString());
                    columnNum++;
                }
            }

            FileStream file = new FileStream(strFilePath, FileMode.Create);
            workbook.Write(file);
            file.Close();
            workbook = null;

            return true;
        }
    }
}
